package dao.impl;

import utils.DBUtil;
import dao.PostinfoDao;
import entity.Postinfo;
import entity.Userinfo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class PostinfoDaoImpl implements PostinfoDao {
    @Override
    public List<Postinfo> queryAllPostinfo() {
        List<Postinfo> list=new ArrayList<>();
        Connection conn = DBUtil.getConnection();
        PreparedStatement stat =null;
        ResultSet res =null;
        String sql="select a.Pid,a.Ucode,a.Pgood,a.Prelay,a.Ptype,a.Utime," +
                "b.Text,b.Timg," +
                "c.Uname,c.Uid from" +
                " Postinfo as a,Content as b,Userinfo as C where a.Pid=b.Pid and c.Ucode=a.Ucode";
        try{
            stat=conn.prepareStatement(sql);
            res=stat.executeQuery();
            while (res.next()){
             int pid=res.getInt(1);
             int ucode=res.getInt(2);
             String pgood =res.getString(3);
             String prelay =res.getString(4);
             String ptype =res.getString(5);
             Date utime =res.getDate(6);

             String text =res.getString(7);
             String timg =res.getString(8);

             String uname=res.getString(9); //用户名
             String uid=res.getString(10);//--账号
                Postinfo postinfo =new Postinfo(pid,ucode,pgood,prelay,ptype,utime,text,timg,uname,uid);
                list.add(postinfo);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtil.close(conn,stat,res);
        }
        return list;
    }

    @Override
    public List<Postinfo> queryPostinfo(Userinfo user) {
        List<Postinfo> list=new ArrayList<>();
        Connection conn=DBUtil.getConnection();
        PreparedStatement stat=null;
        ResultSet res=null;
        String sql="select a.Pid,a.Ucode,a.Pgood,a.Prelay,a.Ptype,a.Utime," +
                "b.Text,b.Timg," +
                "c.Uname,c.Uid from" +
                " Postinfo as a,Content as b,Userinfo as c where a.Pid=b.Pid and c.Ucode=a.Ucode" +
                "and c.Uid=?";
        try{
            stat=conn.prepareStatement(sql);
            stat.setString(1,user.getUid());
            res=stat.executeQuery();
            while (res.next()){
                int pid=res.getInt(1);
                int ucode=res.getInt(2);
                String pgood =res.getString(3);
                String prelay =res.getString(4);
                String ptype =res.getString(5);
                Date utime =res.getDate(6);

                String text =res.getString(7);
                String timg =res.getString(8);

                String uname=res.getString(9); //用户名
                String uid=res.getString(10);//--账号
                Postinfo postinfo =new Postinfo(pid,ucode,pgood,prelay,ptype,utime,text,timg,uname,uid);
                list.add(postinfo);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtil.close(conn,stat,res);
        }
        return list;
    }

    @Override
    public List<Postinfo> queryDatePostinfo(Date utim) {
        List<Postinfo> list=new ArrayList<>();
        Connection conn=DBUtil.getConnection();
        PreparedStatement stat=null;
        ResultSet res=null;
        String sql="select a.Pid,a.Ucode,a.Pgood,a.Prelay,a.Ptype,a.Utime," +
                "b.Text,b.Timg," +
                "c.Uname,c.Uid from" +
                " Postinfo as a,Content as b,Userinfo as c where a.Pid=b.Pid and c.Ucode=a.Ucode" +
                "and a.Utime like '%'+?+'%'";
        try{
            stat=conn.prepareStatement(sql);
            stat.setDate(1, (java.sql.Date) utim);
            res=stat.executeQuery();
            while (res.next()){
                int pid=res.getInt(1);
                int ucode=res.getInt(2);
                String pgood =res.getString(3);
                String prelay =res.getString(4);
                String ptype =res.getString(5);
                Date utime =res.getDate(6);

                String text =res.getString(7);
                String timg =res.getString(8);

                String uname=res.getString(9); //用户名
                String uid=res.getString(10);//--账号
                Postinfo postinfo =new Postinfo(pid,ucode,pgood,prelay,ptype,utime,text,timg,uname,uid);
                list.add(postinfo);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtil.close(conn,stat,res);
        }
        return list;
    }

    @Override
    public int addPost(Postinfo post) {
        int r = 0;
        Connection conn =DBUtil.getConnection();
        PreparedStatement stat =null;
        ResultSet res=null;
        String sql = "insert into Postinfo values (?,?,?,?,?,?,?,?)" ;
        try{
            stat=conn.prepareStatement(sql);
            res=stat.executeQuery();
            while (res.next()){

            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtil.close(conn,stat,null);
        }
        return r;
    }

    @Override
    public int delPost(int pid) {
        return 0;
    }

    @Override
    public int updatePost(Postinfo post) {
        return 0;
    }
}
